<?php

/**
 * @property StudentUser $StudentUser Description
 * @property BankUser $BankUser Description
 * @property Transaction $Transaction Description
 * @property Config $Config Description
 * @property TransactionBankDetail $TransactionBankDetail Description
 */
class EcobankController extends AppController
{
    # Controller Name to be used

    var $name = 'Ecobank';


    # Models to be used
    var $uses = array(
        'BankUser', 'StudentUser', 'BankBranch', 'Transaction', 'Config', 'TransactionBankDetail', 'StudentNotification',
        'BankReportTracking', 'FeeType', 'BankReportTracking', 'BankReportLog', 'BankFailedLogin','BankCreditAuthorization',
        'BankReversalAuthorization','BankBatch','Bank','StudentWallet','AuthenticatedApp'
    );

    # set the layout to use
    var $layout = 'bank_layout';


   /* protected function _response($json_data = array()) {
        return new CakeResponse(array('type' => 'json', 'body' => json_encode($json_data)));
    }*/

    public function beforeFilter() {
        $this->autoRender = false;

        $rules = array("required" => "APP_ID, APP_KEY");
        $validate = $this->_validateParams($rules, $this->request->data);
        if ($validate != "NO_ERROR") {
            return $validate;
        }
        parent::beforeFilter();
        //Only ecobank App ID is allowed
        $app_id = $this->request->data['APP_ID'];
        $app_key = $this->request->data['APP_KEY'];

        $bank_id = 2; //Always Ecobank
        $res = $this->checkAppId($app_id,$app_key,$bank_id);
        if(!$res){
            echo $this->_response(array('response_code' => 1, 'message' => 'Application Not Allowed'));
            exit;
        }

        // check app is providing authentication information
        if( !$this->isAppAuthenticated() ) {
            echo $this->_response(array('response_code' => 1, 'message' => 'Application Not Authenticated'));
            exit;
        }
    }

   /* private function isAppAuthenticated() {

    }*/


    private function getUserBankDetails($bank_branch_id)
    {
        $data = $this->BankBranch->find('first', array(
            'conditions' => array('BankBranch.id' => $bank_branch_id),
            'contain'=>array('Bank'),
            'recursive' => 1
        ));
        return $data;
    }

    private function getTellerInfo($username)
    {
        $data = $this->BankUser->find('first', array(
            'conditions' => array('BankUser.bank_id' => 2,'BankUser.username' => $username),
            'recursive' => -1
        ));
        return $data;
    }

    private function getBranch($branch_code)
    {
        $bank_id = 2; //Always Ecobank
        $data = $this->BankBranch->find('first', array(
            'conditions' => array('BankBranch.branch_code' => $branch_code,'BankBranch.bank_id' => $bank_id),
            'recursive' => -1
        ));
        return $data;
    }


    public function search()
    {
        $this->autoRender = false;

        $data = $this->request->data;
        $rules = array("required" => "index_number, branch_code");
        $validate = $this->_validateParams($rules, $data);
        if ($validate != "NO_ERROR") {
            return $validate;
        }
        $data  = $this->BankUser->sanitize($data);
        $index_number = $data['index_number'];
        $branch_code = $data['branch_code'];
        $branch_data = $this->getBranch($branch_code);
        if(!$branch_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Branch Found',
            );
            return $this->_response($result);
        }
        $bank_branch_id = $branch_data['BankBranch']['id'];
        $bank_info = $this->getUserBankDetails($bank_branch_id);
        $results_api = $this->Api->post('/bank/search', array(
            'index_number' => $index_number,
            'bank_id' => $bank_info['Bank']['id']
        ));

        return $this->_response($results_api);
    }

    /**
     * Credit a Student's Account with a Cash or Cheque Payment
     */
    public function credit_account()
    {
        $this->autoRender = false;

        $data = $this->request->data;
        $payment_method= $data['payment_method'];
        $rules = array("required" => "index_number, teller_id,branch_code,amount,payee_name,payee_phone,payment_method");
        if($payment_method == 'cheque'){
            $rules = array("required" => "index_number, teller_id,branch_code,amount,payee_name,payee_phone,payment_method,payin_cheque_number,payin_cheque_bank");
        }
        $validate = $this->_validateParams($rules, $data);
        if ($validate != "NO_ERROR") {
            return $validate;
        }
        $data  = $this->BankUser->sanitize($data);
        $index_number = $data['index_number'];
        $branch_code = $data['branch_code'];
        $branch_data = $this->getBranch($branch_code);
        if(!$branch_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Branch Found'
            );
            return $this->_response($result);
        }
        $bank_branch_id = $branch_data['BankBranch']['id'];
        $teller_data = $this->getTellerInfo($data['teller_id']);
        if(!$teller_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Teller Found',
            );
            return $this->_response($result);
        }
        $bank_user_id = $teller_data['BankUser']['id'];

        $bank_info = $this->getUserBankDetails($bank_branch_id);
        $amount_tendered = doubleval($data['amount']);
        $branch_id = $bank_info['BankBranch']['id'];
        $payee_name = $data['payee_name'];
        $payee_phone= $data['payee_phone'];
        $payee_address= $data['payee_address'];
        $payment_method= $data['payment_method'];
        $payin_cheque_number= isset($data['payin_cheque_number']) ? $data['payin_cheque_number']: '';
        $payin_cheque_bank = isset($data['payin_cheque_bank']) ? $data['payin_cheque_bank'] : '';

        $post_data = array(
            'index_number' => $index_number,
            'amount_tendered' => $amount_tendered,
            'branch_id' => $branch_id,
            'bank_user_id' => $bank_user_id,
            'payee_name' => $payee_name,
            'payee_phone'=> $payee_phone,
            'payee_address'=> $payee_address,
            'payment_method'=> $payment_method,
            'payin_cheque_number'=> $payin_cheque_number,
            'payin_cheque_bank' => $payin_cheque_bank
        );

        $results_api = $this->Api->post('/bank/credit_account',$post_data );

        return $this->_response($results_api);
    }


    public  function get_credit_transactions()
    {
        $this->autoRender = false;

        $data = $this->request->data;
        if(isset($data['trans_ref_no'])){

        }
        else{
            $rules = array("required" => "start_dt, end_dt");
            $validate = $this->_validateParams($rules, $data);
            if ($validate != "NO_ERROR") {
                return $validate;
            }
        }

        $data  = $this->BankUser->sanitize($data);
        $start_dt = isset($data['start_dt'])?$data['start_dt']:'';
        $end_dt = isset($data['end_dt'])?$data['end_dt']:'';
        //$bank_branch_id = $data['branch_id'];
        //$bank_info = $this->getUserBankDetails($bank_branch_id);
        $bank_id = 2;
        $filter_branch = null;
        $filter_branch_code = isset($data['filter_branch_code'])?$data['filter_branch_code']:null;

        $teller = isset($data['filter_teller_id'])?$data['filter_teller_id']:null;

        $reference = isset($data['trans_ref_no'])?$data['trans_ref_no']:null;;
        if(!isset($data['trans_ref_no'])){
            if($filter_branch_code != null){
                $branch_data = $this->getBranch($filter_branch_code);
                if(!$branch_data){
                    $result = array(
                        'response_code'=>1,
                        'message'=>'No Branch Found'
                    );
                    return $this->_response($result);
                }
                $filter_branch = $branch_data['BankBranch']['id'];
            }

            if($teller != null){
                $teller_data = $this->getTellerInfo($teller);
                if(!$teller_data){
                    $result = array(
                        'response_code'=>1,
                        'message'=>'No Teller Found'
                    );
                    return $this->_response($result);
                }
                $teller = $teller_data['BankUser']['id'];
            }
        }
        $results_api = $this->Api->post('/bank/transactions', array(
            'start_dt' => $start_dt,
            'end_dt' => $end_dt,
            'type' => "credit_account",
            'bank_id' => $bank_id,
            'filter_branch' => $filter_branch,
            'filter_teller' => $teller,
            'filter_trans_ref_no' => $reference
        ));

        return $this->_response($results_api);
    }


    function authorize_credit_transaction(){

        $this->autoRender = false;

        $data = $this->request->data;
        $rules = array("required" => "trans_ref_no, teller_id,branch_code");
        $validate = $this->_validateParams($rules, $data);
        if ($validate != "NO_ERROR") {
            return $validate;
        }
        $data  = $this->BankUser->sanitize($data);
        $trans_ref_no = $data['trans_ref_no'];
        $branch_code = $data['branch_code'];
        $branch_data = $this->getBranch($branch_code);
        if(!$branch_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Branch Found'
            );
            return $this->_response($result);
        }
        $bank_branch_id = $branch_data['BankBranch']['id'];
        $teller_data = $this->getTellerInfo($data['teller_id']);
        if(!$teller_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Teller Found'
            );
            return $this->_response($result);
        }
        $bank_user_id = $teller_data['BankUser']['id'];
        $bank_info = $this->getUserBankDetails($bank_branch_id);
        $bank_id = $bank_info['Bank']['id'];

        $results_api = $this->Api->post('/bank/authorize_transaction', array(
            'trans_ref_no' => $trans_ref_no,
            'bank_id' => $bank_id,
            'bank_user_id' => $bank_user_id
        ));

        return $this->_response($results_api);
    }



    public function revert_credit_transaction()
    {
        $this->autoRender = false;
        $data = $this->request->data;
        $data  = $this->BankUser->sanitize($data);
        $transaction_id = $data['transaction_id'];
        $debit_amt = $data['debit_amt'];
        $reason = $data['reason'];
        $branch_code = $data['branch_code'];
        $branch_data = $this->getBranch($branch_code);
        if(!$branch_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Branch Found'
            );
            return $this->_response($result);
        }
        $bank_branch_id = $branch_data['BankBranch']['id'];
        $teller_data = $this->getTellerInfo($data['teller_id']);
        if(!$teller_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Teller Found'
            );
            return $this->_response($result);
        }
        $bank_user_id = $teller_data['BankUser']['id'];
        $bank_info = $this->getUserBankDetails($bank_branch_id);
        $bank_id = $bank_info['BankBranch']['id'];

        $results_api = $this->Api->post('/bank/revert_transaction', array(
            'bank_id' => $bank_id,
            'bank_user_id' => $bank_user_id,
            'transaction_id' => $transaction_id,
            'debit_amt'=> $debit_amt,
            'reason'=> $reason
        ));

        return $this->_response($results_api);
    }


    public function get_fee_payment_transactions()
    {
        $this->autoRender = false;

        $data = $this->request->data;
        $rules = array("required" => "teller_id,branch_code");
        $validate = $this->_validateParams($rules, $data);
        if ($validate != "NO_ERROR") {
            return $validate;
        }
        $data  = $this->BankUser->sanitize($data);
        $branch_code = $data['branch_code'];
        $branch_data = $this->getBranch($branch_code);
        if(!$branch_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Branch Found'
            );
            return $this->_response($result);
        }
        $bank_branch_id = $branch_data['BankBranch']['id'];
        $teller_data = $this->getTellerInfo($data['teller_id']);
        if(!$teller_data){
            $result = array(
                'response_code'=>1,
                'message'=>'No Teller Found'
            );
            return $this->_response($result);
        }
        $bank_user_id = $teller_data['BankUser']['id'];
        $bank_info = $this->getUserBankDetails($bank_branch_id);
        $bank_id = $bank_info['Bank']['id'];

        $bank_trans = 'internal';
        $bank_branch_code = $bank_info['BankBranch']['branch_code'];
        $batch_no_data = $this->BankBatch->getBatchNo($bank_id,$bank_trans);
        $batch_id = null;
        $batch_no = null;
        if($batch_no_data){
            $batch_id = $batch_no_data['id'];
            $batch_no = $batch_no_data['next_batch_no'];
        }
        $batch_name = 'ups'.$batch_no;

        $last_trans_data = $this->BankReportTracking->getLastReportRecord($bank_id,$bank_trans);

        $start_report_id = null;
        $last_report_id = null;
        if ($last_trans_data) {
            $last_trans_no = $last_trans_data['last_trans_id'];
            $last_report_id = $last_trans_data['id'];
            $start_report_id = $last_trans_no;
        }

        if($batch_id == null){
            $trans_data = false;
        }
        elseif($start_report_id == null){
            $trans_data = false;
        }
        else{
            $debit_account = $bank_info['Bank']['withholding_acc_no']; //Withholding Account
            $trans_data = $this->__get_report_data('new', $start_report_id,null,$bank_id,$bank_branch_code,$bank_trans);

            foreach ($trans_data as $key => $value) {
                $trans_data[$key]['Transaction']['batch_no'] = $batch_name;
                $trans_data[$key]['Transaction']['withholding_account'] = $debit_account;
            }
        }

        if ($trans_data) {

            $reversed_arr = $trans_data;
            $reversed_arr = array_reverse($reversed_arr, true);
            $first_trans_id = $trans_data[0]['Transaction']['id'];
            $last_trans_id = $reversed_arr[count($trans_data) - 1]['Transaction']['id'];

            //save last transaction id
            $this->BankReportTracking->setLastTransId($last_report_id,$bank_id,$last_trans_id,$bank_trans);
            //log the fisrt and last trans id current one for future reuse
            $this->BankReportLog->createReportLog($bank_trans,$bank_id,$bank_user_id,$first_trans_id,$last_trans_id,'','','',$batch_name);
            //Set the next batch number for next time
            $next_batch_no = intval($batch_no) + 1;
            $this->BankBatch->setBatchNo($batch_id,$next_batch_no,$bank_trans);
        }

        $result = array(
            'response_code'=>1,
            'message'=>'No New Fee Payment Transactions',
            'data'=>array()
        );
        if ($trans_data) {
            $result = array(
                'response_code'=>0,
                'message'=>'New Fee Payment Transactions Found',
                'data'=>$trans_data
            );
        }

        return $this->_response($result);
    }


    public function __get_report_data($type, $start, $end = null,$bank_id,$branch_code,$bank_trans)
    {
        $results_api = $this->Api->post('/bank/get_fee_payments', array(
            'type'=>$type,
            'start'=>$start,
            'end'=>$end,
            'bank_id' => $bank_id,
            'branch_code' => $branch_code
        ));

       // if ($results_api['response_code'] == 0) {
           // return array('status' => 'success', 'message' => $results_api['message'],'data'=>$results_api['data']);
       // }
       // else {
           // return array('status' => 'failed', 'message' => $results_api['message'],'data'=>$results_api['data']);
       // }

        $trans_data = $results_api['data'];

        return $trans_data;
    }



    private function _convertToExcel($param)
    {
        ini_set("memory_limit", "512M");
        set_time_limit('1200');

        $count = 0;
        $total_amount = 0.00;
        $maker_id = $param[0]['Transaction']['maker_id'];
        $user_id = $param[0]['Transaction']['user_id'];
        $checker_id = $param[0]['Transaction']['checker_id'];
        $maker_time_stamp = $param[0]['Transaction']['maker_time_stamp'];
        $batch_no = $param[0]['Transaction']['batch_no'];
        $batch_code_master = $param[0]['Transaction']['branch_code'];
        $filename = "UPSATXN" . date("Ymd", strtotime($maker_time_stamp));
        //$debit_account = '25008990';//Withholding Account
        $debit_account = $param[0]['Transaction']['withholding_account']; //Withholding Account
        $first_row_constant_master = 'fcclive.DETB_UPLOAD_MASTER';
        $first_row_constant_detail = 'fcclive.DETB_UPLOAD_DETAIL';

        foreach ($param as $val) {
            $count = $count + count($val['TransactionItem']);
            foreach ($val['TransactionItem'] as $item) {
                $total_amount = $total_amount + doubleval($item['credit_amt']);
            }
        }
        $total_entries = $count * 2;

        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
        $cacheSettings = array(' memoryCacheSize ' => '8MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $objPHPExcel = new PHPExcel();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Interpay")
            ->setLastModifiedBy("Interpay")
            ->setTitle("Office 2007 XLSX Interpay Document")
            ->setSubject("Office 2007 XLSX Interpay Document")
            ->setDescription("Interpay document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Interpay result file");

        // Set default font
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', $first_row_constant_master);
        $objPHPExcel->getActiveSheet()->getStyle('A2:AD2')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->setCellValue('B1', '~~END~~')
            ->setCellValue('A2', 'BRANCH_CODE')
            ->setCellValue('B2', 'SOURCE_CODE')
            ->setCellValue('C2', 'BATCH_NO')
            ->setCellValue('D2', 'TOTAL_ENTRIES')
            ->setCellValue('E2', 'UPLOADED_ENTRIES')
            ->setCellValue('F2', 'BALANCING')
            ->setCellValue('G2', 'BATCH_DESC')
            ->setCellValue('H2', 'MIS_REQUIRED')
            ->setCellValue('I2', 'AUTO_AUTH')
            ->setCellValue('J2', 'GL_OFFSET_ENTRY_REQD')
            ->setCellValue('K2', 'UDF_UPLOAD_REQD')
            ->setCellValue('L2', 'OFFSET_GL')
            ->setCellValue('M2', 'TXN_CODE')
            ->setCellValue('N2', 'DR_ENT_TOTAL')
            ->setCellValue('O2', 'CR_ENT_TOTAL')
            ->setCellValue('P2', 'USER_ID')
            ->setCellValue('Q2', 'UPLOAD_STAT')
            ->setCellValue('R2', 'JOBNO')
            ->setCellValue('S2', 'SYSTEM_BATCH')
            ->setCellValue('T2', 'POSITION_REQD')
            ->setCellValue('U2', 'MAKER_ID')
            ->setCellValue('V2', 'MAKER_DT_STAMP')
            ->setCellValue('W2', 'CHECKER_ID')
            ->setCellValue('X2', 'CHECKER_DT_STAMP')
            ->setCellValue('Y2', 'MOD_NO')
            ->setCellValue('Z2', 'AUTH_STAT')
            ->setCellValue('AA2', 'RECORD_STAT')
            ->setCellValue('AB2', 'ONCE_AUTH')
            ->setCellValue('AC2', 'UPLOAD_DATE')
            ->setCellValue('AD2', 'UPLOAD_FILE_NAME')
            ->setCellValue('AE2', '');

        //writing main data for sheet one
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A3', $batch_code_master, PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('B3', $filename) /* sourcecode = should be same as filename, unique*/
            ->setCellValueExplicit('C3', $batch_no, PHPExcel_Cell_DataType::TYPE_STRING) /* batchcode_should be an auto incrementig serial*/
            ->setCellValue('D3', $total_entries) /* total entries = should be total number of transactions */
            ->setCellValue('E3', '0')
            ->setCellValue('F3', 'N')
            ->setCellValue('G3', $filename) /* batch description-- should be the same as filename*/
            ->setCellValue('H3', 'N')
            ->setCellValue('I3', 'N')
            ->setCellValue('J3', 'N')
            ->setCellValue('K3', 'N')
            ->setCellValue('L3', '')
            ->setCellValue('M3', '534')
            ->setCellValue('N3', $total_amount) /* should be total amount debited */
            ->setCellValue('O3', $total_amount) /* should be total amount credited*/
            ->setCellValue('P3', $user_id)
            ->setCellValue('Q3', 'Y')
            ->setCellValue('R3', '1')
            ->setCellValue('S3', 'N')
            ->setCellValue('T3', 'N')
            ->setCellValue('U3', $maker_id) /* MAKER ID*/
            ->setCellValue('V3', date("d-M-Y", strtotime($maker_time_stamp))) /*date file was created format (12-Jun-2012) */
            ->setCellValue('W3', $checker_id) /* CHECKER ID*/
            ->setCellValue('X3', '')
            ->setCellValue('Y3', '1')
            ->setCellValue('Z3', 'U')
            ->setCellValue('AA3', 'O')
            ->setCellValue('AB3', 'Y')
            ->setCellValue('AC3', '')
            ->setCellValue('AD3', '')
            ->setCellValue('AE3', '~~END~~');

        // change styling for a group of cells
        $objPHPExcel->getActiveSheet()->getStyle('A4:AE4')->getFont()->setBold(true);


        $objPHPExcel->getActiveSheet()->setCellValue('A4', '~~END~~')
            ->setCellValue('B4', '~~END~~')
            ->setCellValue('C4', '~~END~~')
            ->setCellValue('D4', '~~END~~')
            ->setCellValue('E4', '~~END~~')
            ->setCellValue('F4', '~~END~~')
            ->setCellValue('G4', '~~END~~')
            ->setCellValue('H4', '~~END~~')
            ->setCellValue('I4', '~~END~~')
            ->setCellValue('J4', '~~END~~')
            ->setCellValue('K4', '~~END~~')
            ->setCellValue('L4', '~~END~~')
            ->setCellValue('M4', '~~END~~')
            ->setCellValue('N4', '~~END~~')
            ->setCellValue('O4', '~~END~~')
            ->setCellValue('P4', '~~END~~')
            ->setCellValue('Q4', '~~END~~')
            ->setCellValue('R4', '~~END~~')
            ->setCellValue('S4', '~~END~~')
            ->setCellValue('T4', '~~END~~')
            ->setCellValue('U4', '~~END~~')
            ->setCellValue('V4', '~~END~~')
            ->setCellValue('W4', '~~END~~')
            ->setCellValue('X4', '~~END~~')
            ->setCellValue('Y4', '~~END~~')
            ->setCellValue('Z4', '~~END~~')
            ->setCellValue('AA4', '~~END~~')
            ->setCellValue('AB4', '~~END~~')
            ->setCellValue('AC4', '~~END~~')
            ->setCellValue('AD4', '~~END~~')
            ->setCellValue('AE4', '~~END~~');
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Upload_Master');

        /** Lock this sheet */
        $objPHPExcel->getActiveSheet()->getProtection()->setPassword('los enuve aspectos');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

        // create another sheet for upload Details
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex(1);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', $first_row_constant_detail);

        $objPHPExcel->getActiveSheet()->getStyle('A2:BI2')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->setCellValue('B1', '~~END~~')
            ->setCellValue('A2', 'INSTRUMENT_NO')
            ->setCellValue('B2', 'FIN_CYCLE')
            ->setCellValue('C2', 'PERIOD_CODE')
            ->setCellValue('D2', 'MIS_CODE')
            ->setCellValue('E2', 'REL_CUST')
            ->setCellValue('F2', 'ADDL_TEXT')
            ->setCellValue('G2', 'MIS_GROUP')
            ->setCellValue('H2', 'DW_AC_NO')
            ->setCellValue('I2', 'ACCOUNT_NEW')
            ->setCellValue('J2', 'TXN_MIS_1')
            ->setCellValue('K2', 'TXN_MIS_2')
            ->setCellValue('L2', 'TXN_MIS_3')
            ->setCellValue('M2', 'TXN_MIS_4')
            ->setCellValue('N2', 'TXN_MIS_5')
            ->setCellValue('O2', 'TXN_MIS_6')
            ->setCellValue('P2', 'TXN_MIS_7')
            ->setCellValue('Q2', 'TXN_MIS_8')
            ->setCellValue('R2', 'TXN_MIS_9')
            ->setCellValue('S2', 'TXN_MIS_10')
            ->setCellValue('T2', 'COMP_MIS_1')
            ->setCellValue('U2', 'COMP_MIS_2')
            ->setCellValue('V2', 'COMP_MIS_3')
            ->setCellValue('W2', 'COMP_MIS_4')
            ->setCellValue('X2', 'COMP_MIS_5')
            ->setCellValue('Y2', 'COMP_MIS_6')
            ->setCellValue('Z2', 'COMP_MIS_7')
            ->setCellValue('AA2', 'COMP_MIS_8')
            ->setCellValue('AB2', 'COMP_MIS_9')
            ->setCellValue('AC2', 'COMP_MIS_10')
            ->setCellValue('AD2', 'COST_CODE1')
            ->setCellValue('AE2', 'COST_CODE2')
            ->setCellValue('AF2', 'COST_CODE3')
            ->setCellValue('AG2', 'COST_CODE4')
            ->setCellValue('AH2', 'COST_CODE5')
            ->setCellValue('AI2', 'MIS_HEAD')
            ->setCellValue('AJ2', 'RELATED_ACCOUNT')
            ->setCellValue('AK2', 'RELATED_REF')
            ->setCellValue('AL2', 'POOL_CODE')
            ->setCellValue('AM2', 'REF_RATE')
            ->setCellValue('AN2', 'CALC_METHOD')
            ->setCellValue('AO2', 'BATCH_NO')
            ->setCellValue('AP2', 'MIS_FLAG')
            ->setCellValue('AQ2', 'MIS_GROUP_TXN')
            ->setCellValue('AR2', 'BRANCH_CODE')
            ->setCellValue('AS2', 'SOURCE_CODE')
            ->setCellValue('AT2', 'CURR_NO')
            ->setCellValue('AU2', 'UPLOAD_STAT')
            ->setCellValue('AV2', 'CCY_CD')
            ->setCellValue('AW2', 'INITIATION_DATE')
            ->setCellValue('AX2', 'AMOUNT')
            ->setCellValue('AY2', 'ACCOUNT')
            ->setCellValue('AZ2', 'ACCOUNT_BRANCH')
            ->setCellValue('BA2', 'TXN_CODE')
            ->setCellValue('BB2', 'DR_CR')
            ->setCellValue('BC2', 'LCY_EQUIVALENT')
            ->setCellValue('BD2', 'EXCH_RATE')
            ->setCellValue('BE2', 'VALUE_DATE')
            ->setCellValue('BF2', 'EXTERNAL_REF_NO')
            ->setCellValue('BG2', 'RESERVED_FUNDS_REF')
            ->setCellValue('BH2', 'DELETE_STAT')
            ->setCellValue('BI2', 'TXT_FILE_NAME')
            ->setCellValue('BJ2', '');

        //insert the data
        $count2 = 0;
        $curent_no = 1;
        $it = 3;
        foreach ($param as $val) {
            $stud_fullname = $val['StudentUser']['fname'] . ' ' . $val['StudentUser']['mname'] . ' ' . $val['StudentUser']['lname'];
            $index_number = $val['StudentUser']['index_number'];
            $initiation_date = date("d-M-Y", strtotime($val['Transaction']['created']));
            $count2 = $count2 + count($val['TransactionItem']);
            foreach ($val['TransactionItem'] as $item) {
                $amt = $item['credit_amt'];
                $trans_currency = $item['trans_currency'];
                $feetype_decs = strtoupper($item['FeeType']['description'] . ' BY ' . $index_number . '-' . $stud_fullname);
                $credit_acct = $item['FeeType']['account_no'];
                $bank_branch = strtoupper($item['BankBranch']['branch_name']);
                $branch_code = strtoupper($item['BankBranch']['branch_code']);

                //Credit Transaction
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $it, 'FY' . date("Y", strtotime($maker_time_stamp)))
                    ->setCellValue('C' . $it, 'P' . date("m", strtotime($maker_time_stamp)))
                    ->setCellValue('F' . $it, $feetype_decs)//feetype description 'By' indexnumber-student full name
                    ->setCellValueExplicit('AO' . $it, $batch_no, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValueExplicit('AR' . $it, $branch_code, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('AS' . $it, $filename)
                    ->setCellValue('AT' . $it, $curent_no++)
                    ->setCellValue('AV' . $it, $trans_currency)
                    ->setCellValue('AW' . $it, $initiation_date) //Initaition Date
                    ->setCellValue('AX' . $it, $amt)
                    //->setCellValue('AY'.$it, )
                    ->setCellValueExplicit('AY' . $it, $credit_acct, PHPExcel_Cell_DataType::TYPE_STRING)//credit Acount number
                    //->setCellValue('AZ'.$it, '024')
                    ->setCellValueExplicit('AZ' . $it, '024', PHPExcel_Cell_DataType::TYPE_STRING)// Account branch ,BRANCH CODE the account is being held 024
                    ->setCellValue('BA' . $it, '230')
                    ->setCellValue('BB' . $it, 'C')//CREDIT
                    ->setCellValue('BC' . $it, $amt)
                    ->setCellValue('BE' . $it, date("d-M-Y", strtotime($maker_time_stamp))) //Value Date
                    ->setCellValue('BJ' . $it, '~~END~~');
                $it++;
                //Debit Transaction
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $it, 'FY' . date("Y", strtotime($maker_time_stamp)))
                    ->setCellValue('C' . $it, 'P' . date("m", strtotime($maker_time_stamp)))
                    ->setCellValue('F' . $it, $feetype_decs)
                    ->setCellValueExplicit('AO' . $it, $batch_no, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValueExplicit('AR' . $it, $branch_code, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('AS' . $it, $filename)
                    ->setCellValue('AT' . $it, $curent_no++)
                    ->setCellValue('AV' . $it, $trans_currency)
                    ->setCellValue('AW' . $it, $initiation_date) //Initaition Date
                    ->setCellValue('AX' . $it, $amt)
                    // ->setCellValue('AY'.$it, $debit_account) //debit Acount number
                    ->setCellValueExplicit('AY' . $it, $debit_account, PHPExcel_Cell_DataType::TYPE_STRING)//credit Acount number
                    ->setCellValueExplicit('AZ' . $it, '024', PHPExcel_Cell_DataType::TYPE_STRING)// Account branch ,BRANCH CODE the account is being held 024
                    ->setCellValue('BA' . $it, '230')
                    ->setCellValue('BB' . $it, 'D')//CREDIT
                    ->setCellValue('BC' . $it, $amt)
                    ->setCellValue('BE' . $it, date("d-M-Y", strtotime($maker_time_stamp))) //Value Date
                    ->setCellValue('BJ' . $it, '~~END~~');

                $it++;
            }
        }

        $objPHPExcel->getActiveSheet()->setCellValue('A' . $it, '~~END~~')
            ->setCellValue('B' . $it, '~~END~~')
            ->setCellValue('C' . $it, '~~END~~')
            ->setCellValue('D' . $it, '~~END~~')
            ->setCellValue('E' . $it, '~~END~~')
            ->setCellValue('F' . $it, '~~END~~')
            ->setCellValue('G' . $it, '~~END~~')
            ->setCellValue('H' . $it, '~~END~~')
            ->setCellValue('I' . $it, '~~END~~')
            ->setCellValue('J' . $it, '~~END~~')
            ->setCellValue('K' . $it, '~~END~~')
            ->setCellValue('L' . $it, '~~END~~')
            ->setCellValue('M' . $it, '~~END~~')
            ->setCellValue('N' . $it, '~~END~~')
            ->setCellValue('O' . $it, '~~END~~')
            ->setCellValue('P' . $it, '~~END~~')
            ->setCellValue('Q' . $it, '~~END~~')
            ->setCellValue('R' . $it, '~~END~~')
            ->setCellValue('S' . $it, '~~END~~')
            ->setCellValue('T' . $it, '~~END~~')
            ->setCellValue('U' . $it, '~~END~~')
            ->setCellValue('V' . $it, '~~END~~')
            ->setCellValue('W' . $it, '~~END~~')
            ->setCellValue('X' . $it, '~~END~~')
            ->setCellValue('Y' . $it, '~~END~~')
            ->setCellValue('Z' . $it, '~~END~~')
            ->setCellValue('AA' . $it, '~~END~~')
            ->setCellValue('AB' . $it, '~~END~~')
            ->setCellValue('AC' . $it, '~~END~~')
            ->setCellValue('AD' . $it, '~~END~~')
            ->setCellValue('AE' . $it, '~~END~~')
            ->setCellValue('AF' . $it, '~~END~~')
            ->setCellValue('AG' . $it, '~~END~~')
            ->setCellValue('AH' . $it, '~~END~~')
            ->setCellValue('AI' . $it, '~~END~~')
            ->setCellValue('AJ' . $it, '~~END~~')
            ->setCellValue('AK' . $it, '~~END~~')
            ->setCellValue('AL' . $it, '~~END~~')
            ->setCellValue('AM' . $it, '~~END~~')
            ->setCellValue('AN' . $it, '~~END~~')
            ->setCellValue('AO' . $it, '~~END~~')
            ->setCellValue('AP' . $it, '~~END~~')
            ->setCellValue('AQ' . $it, '~~END~~')
            ->setCellValue('AR' . $it, '~~END~~')
            ->setCellValue('AS' . $it, '~~END~~')
            ->setCellValue('AT' . $it, '~~END~~')
            ->setCellValue('AU' . $it, '~~END~~')
            ->setCellValue('AV' . $it, '~~END~~')
            ->setCellValue('AW' . $it, '~~END~~')
            ->setCellValue('AX' . $it, '~~END~~')
            ->setCellValue('AY' . $it, '~~END~~')
            ->setCellValue('AZ' . $it, '~~END~~')
            ->setCellValue('BA' . $it, '~~END~~')
            ->setCellValue('BB' . $it, '~~END~~')
            ->setCellValue('BC' . $it, '~~END~~')
            ->setCellValue('BD' . $it, '~~END~~')
            ->setCellValue('BE' . $it, '~~END~~')
            ->setCellValue('BF' . $it, '~~END~~')
            ->setCellValue('BG' . $it, '~~END~~')
            ->setCellValue('BH' . $it, '~~END~~')
            ->setCellValue('BI' . $it, '~~END~~')
            ->setCellValue('BJ' . $it, '~~END~~');

        $objPHPExcel->getActiveSheet()->getStyle('A' . $it . ':BJ' . $it)->getFont()->setBold(true);

        // Rename worksheet 2 to upload Detail
        $objPHPExcel->getActiveSheet()->setTitle('Upload_Detail');

        /** Lock this sheet */
        $objPHPExcel->getActiveSheet()->getProtection()->setPassword('los enuve aspectos');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        return array(
            'excel_obj' => $objPHPExcel,
            'filename' => $filename
        );
    }


}